"""
Case Type   : 兼容M*数据库subdate函数
Case Name   : SUBDATE(text date, INTERVAL expr unit)函数，参数1为datetime类型，
              interval单位测试
Create At   : 2023.01.29
Owner       : z@wan005
Description :
    1、第一入参为datetime类型,第2个参数时间单位为year
    2、第一入参为datetime类型,第2个参数时间单位为month
    3、第一入参为datetime类型,第2个参数时间单位为day
    4、第2个参数时间单位为hour
    5、第2个参数时间单位为minute
    6、第2个参数时间单位为second
    7、第2个参数时间单位为day to hour
    8、第2个参数时间单位为day to minute
    9、第2个参数时间单位为day to second
    10、第2个参数时间单位为hour to minute
    11、第2个参数时间单位为hour to second
    12、清理环境
Expect      :
    1、报错
    2、报错
    3、成功
    4、成功
    5、成功
    6、成功
    7、成功
    8、成功
    9、成功
    10、成功
    11、成功
    12、成功
History     :modified by zhanghuan96，2024.06.28，优化断言
             modified by zhanghuan，2024.8.2，适配最新代码，修改断言
             modified by zhanghuan96，2024.8.26，IAHP3M问题修复，代码变更
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest07(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_subdate_0014"
        self.expect = "ERROR:  time field value out of range"

    def test_subdate(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.log.info('----前置条件：创建兼容b库---')
        sql_cmd1 = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility 'b';"
        self.log.info(sql_cmd1)
        sql_res1 = self.sh_primary.execut_db_sql(sql_cmd1)
        self.log.info(sql_res1)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res1)

        text = '--step1:第一入参为datetime类型,第2个参数时间单位为year;expect:成功--'
        self.log.info(text)
        sql_cmd2 = "SELECT SUBDATE('2022-01-01 14:59:59',INTERVAL 1 YEAR);" \
                  "SELECT SUBDATE('2022-01-01 14:59:59',INTERVAL 21.9 YEAR);" \
                  "SELECT SUBDATE('2022-01-01 14:59:59',INTERVAL 22.4 YEAR);"
        self.log.info(sql_cmd2)
        sql_res2 = self.sh_primary.execut_db_sql(sql_cmd2,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res2)
        self.assertTrue(len(sql_res2.strip().splitlines()) >= 12, '行数不到14行')
        self.assertEqual('2021-01-01 14:59:59',
                         sql_res2.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('2000-01-01 14:59:59',
                         sql_res2.splitlines()[7].strip(), '执行失败' + text)
        self.assertEqual('2000-01-01 14:59:59',
                         sql_res2.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step2:第一入参为datetime类型,第2个参数时间单位为month;expect:成功--'
        self.log.info(text)
        sql_cmd3 = "SELECT SUBDATE('2022-01-01 14:59:59', INTERVAL 1 month);" \
                  "SELECT SUBDATE" \
                  "('2022-01-01 14:59:59.999999', INTERVAL 21.9 month);" \
                  "SELECT SUBDATE" \
                  "('2022-01-01 14:59:59.9', INTERVAL 22.444 month);"
        self.log.info(sql_cmd3)
        sql_res3 = self.sh_primary.execut_db_sql(sql_cmd3,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res3)
        self.assertTrue(len(sql_res3.strip().splitlines()) >= 14, '行数不到14行')
        self.assertEqual('2021-12-01 14:59:59',
                         sql_res3.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('2020-03-01 14:59:59.999999',
                         sql_res3.splitlines()[7].strip(), '执行失败' + text)
        self.assertEqual('2020-03-01 14:59:59.9',
                         sql_res3.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step3:第一入参为datetime类型,第2个参数时间单位为day;expect:成功--'
        self.log.info(text)
        sql_cmd4 = "SELECT SUBDATE('2022-01-01 14:59:59', INTERVAL 1 day);" \
                  "SELECT SUBDATE('2022-01-01 14:59:59', INTERVAL 21.9 day);" \
                  "SELECT SUBDATE('2022-01-01 14:59:59', INTERVAL 22.4 day);"
        self.log.info(sql_cmd4)
        sql_res4 = self.sh_primary.execut_db_sql(sql_cmd4,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res4)
        self.assertTrue(len(sql_res4.strip().splitlines()) >= 14, '行数不到14行')
        self.assertEqual('2021-12-31 14:59:59',
                         sql_res4.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('2021-12-10 14:59:59',
                         sql_res4.splitlines()[7].strip(), '执行失败' + text)
        self.assertEqual('2021-12-10 14:59:59',
                         sql_res4.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step4:第2个参数时间单位为hour;expect:成功--'
        self.log.info(text)
        sql_cmd5 = "SELECT SUBDATE" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 1 hour);" \
                  "SELECT SUBDATE" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 1.999 hour);" \
                  "SELECT SUBDATE" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 1.444444 hour);"
        self.log.info(sql_cmd5)
        sql_res5 = self.sh_primary.execut_db_sql(sql_cmd5,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res5)
        self.assertTrue(len(sql_res5.strip().splitlines()) >= 14, '行数不到14行')
        self.assertEqual('9999-12-31 22:59:59.999999',
                         sql_res5.splitlines()[2].strip(),
                         '执行失败' + text)
        self.assertEqual('9999-12-31 21:59:59.999999',
                         sql_res5.splitlines()[7].strip(),
                         '执行失败' + text)
        self.assertEqual('9999-12-31 22:59:59.999999',
                         sql_res5.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step5:第2个参数时间单位为minute;expect:成功--'
        self.log.info(text)
        sql_cmd6 = "SELECT SUBDATE" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 59 minute);" \
                  "SELECT SUBDATE" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 59.9 minute);" \
                  "SELECT SUBDATE" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 59.444 minute);"
        self.log.info(sql_cmd6)
        sql_res6 = self.sh_primary.execut_db_sql(sql_cmd6,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res6)
        self.assertTrue(len(sql_res6.strip().splitlines()) >= 14, '行数不到14行')
        self.assertEqual('9999-12-31 23:00:59.999999',
                         sql_res6.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('9999-12-31 22:59:59.999999',
                         sql_res6.splitlines()[7].strip(),
                         '执行失败' + text)
        self.assertEqual('9999-12-31 23:00:59.999999',
                         sql_res6.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step6:第2个参数时间单位为second;expect:成功--'
        self.log.info(text)
        sql_cmd7 = "SELECT SUBDATE" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 59 second);" \
                  "SELECT SUBDATE" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 59.999 second);" \
                  "SELECT SUBDATE" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 59.4445 second);"
        self.log.info(sql_cmd7)
        sql_res7 = self.sh_primary.execut_db_sql(sql_cmd7,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res7)
        self.assertTrue(len(sql_res7.strip().splitlines()) >= 14, '行数不到14行')
        self.assertEqual('9999-12-31 23:59:00.999999',
                         sql_res7.splitlines()[2].strip(), '执行失败' + text)
        self.assertEqual('9999-12-31 23:59:00.000999',
                         sql_res7.splitlines()[7].strip(),
                         '执行失败' + text)
        self.assertEqual('9999-12-31 23:59:00.555499',
                         sql_res7.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step7:第2个参数时间单位为day to hour;expect:成功--'
        self.log.info(text)
        sql_cmd8 = "set dolphin.support_interval_to =on;" \
                    "select subdate" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 1 day to hour);" \
                  "select subdate" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 1.5 day to hour);" \
                  "select subdate" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 1.4 day to hour) ;"
        self.log.info(sql_cmd8)
        sql_res8 = self.sh_primary.execut_db_sql(sql_cmd8,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res8)
        self.assertEqual('9999-12-31 22:59:59.999999',
                         sql_res8.splitlines()[3].strip(), '执行失败' + text)
        self.assertEqual('9999-12-30 18:59:59.999999',
                         sql_res8.splitlines()[8].strip(), '执行失败' + text)
        self.assertEqual('9999-12-30 19:59:59.999999',
                         sql_res8.splitlines()[-2].strip(), '执行失败' + text)

        text = '--step8:interval的时间单位为day to minute;expect:成功--'
        self.log.info(text)
        sql_cmd9 = "set dolphin.support_interval_to =on;" \
                    "select subdate" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 1 day to minute);" \
                  "select subdate" \
                  "('9999-12-31 23:59:59.999999', " \
                  "INTERVAL 1.9 day to minute);" \
                  "select subdate('9999-12-31 23:59:59.999999', " \
                  "INTERVAL 1.4 day to minute);"
        self.log.info(sql_cmd9)
        sql_res9 = self.sh_primary.execut_db_sql(sql_cmd9,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res9)
        self.assertEqual('9999-12-31 23:58:59.999999',
                         sql_res9.splitlines()[3].strip(), '执行失败' + text)
        self.assertEqual('9999-12-31 22:50:59.999999',
                         sql_res9.splitlines()[8].strip(),
                         '执行失败' + text)
        self.assertEqual('9999-12-31 22:55:59.999999',
                         sql_res9.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step9:interval的时间单位为day to second;expect:成功--'
        self.log.info(text)
        sql_cmd10 = "set dolphin.support_interval_to =on;" \
                     "select subdate" \
                  "('9999-12-31 23:59:59.999999', INTERVAL 1 day to second);" \
                  "select subdate" \
                  "('9999-12-31 23:59:59.999999', " \
                  "INTERVAL 1.9 day to second);" \
                  "select subdate('9999-12-31 23:59:59.999999'," \
                  " INTERVAL 1.4 day to second);"
        self.log.info(sql_cmd10)
        sql_res10 = self.sh_primary.execut_db_sql(sql_cmd10,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res10)
        self.assertTrue(len(sql_res10.strip().splitlines()) >= 14, '行数不到14行')
        self.assertEqual('9999-12-31 23:59:58.999999',
                         sql_res10.splitlines()[3].strip(), '执行失败' + text)
        self.assertEqual('9999-12-31 23:58:50.999999',
                         sql_res10.splitlines()[8].strip(),
                         '执行失败' + text)
        self.assertEqual('9999-12-31 23:58:55.999999',
                         sql_res10.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step10:第2个参数时间单位为hour to minute;expect:成功--'
        self.log.info(text)
        sql_cmd11 = "set dolphin.support_interval_to =on;" \
                     "select subdate" \
                  "('9999-12-31 23:59:59.999999', " \
                  "INTERVAL 1 hour to minute);" \
                  "select subdate('9999-12-31 23:59:59.999999', " \
                  "INTERVAL 1.9 hour to minute);" \
                  "select subdate('9999-12-31 23:59:59.999999', " \
                  "INTERVAL 1.4 hour to minute) ;"
        self.log.info(sql_cmd11)
        sql_res11 = self.sh_primary.execut_db_sql(sql_cmd11,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res11)
        self.assertEqual('9999-12-31 23:58:59.999999',
                         sql_res11.splitlines()[3].strip(), '执行失败' + text)
        self.assertEqual('9999-12-31 22:50:59.999999',
                         sql_res11.splitlines()[8].strip(),
                         '执行失败' + text)
        self.assertEqual('9999-12-31 22:55:59.999999',
                         sql_res11.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '--step11:interval的时间单位为hour to second;expect:成功--'
        self.log.info(text)
        sql_cmd12 = "set dolphin.support_interval_to =on;" \
                     "select subdate" \
                  "('9999-12-31 23:59:59.999999', " \
                  "INTERVAL 1 hour to second);" \
                  "select subdate('9999-12-31 23:59:59.999999', " \
                  "INTERVAL 1 hour to second);"
        self.log.info(sql_cmd12)
        sql_res12 = self.sh_primary.execut_db_sql(sql_cmd12,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res12)

        self.assertEqual(2, sql_res12.count('9999-12-31 23:59:58.999999'),
                         '执行失败' + text)

    def tearDown(self):
        text = '----step12:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop database {self.db_name};"
        self.log.info(sql_cmd)
        drop_res1 = self.sh_primary.execut_db_sql(sql_cmd,
                                                  dbname='postgres')
        self.log.info(drop_res1)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_res1,
                      '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
